package org.esgi.user.model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * @author fabrice
 * 
 */
public class UserDao {

	public final static String HOST = "127.0.0.1";
	public final static String PORT = "3306";
	public final static String SCHEMA = "j2ee";
	public final static String LOGIN = "root";
	public final static String PASSWORD = "root";

	private static UserDao _instance = new UserDao();
	private Connection connection = null;

	private UserDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://"
					+ HOST + ":" + PORT + "/" + SCHEMA, LOGIN, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static UserDao getInstance() {
		return _instance;
	}

	PreparedStatement insert = null;

	// Implementing CRUD Operation. (Create Remove Update Display)
	public boolean insert(User user) throws SQLException {
		if (null == insert) {
			insert = connection
					.prepareStatement("INSERT INTO users(login,password,status,created_at) VALUES(?,?,?,NOW())");
		}
		insert.setString(1, user.getLogin());
		insert.setString(2, user.getPassword());
		insert.setInt(3, user.getStatus());
		try {
			insert.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	Map<Integer, PreparedStatement> updatePs = new HashMap<Integer, PreparedStatement>();

	/**
	 * Update user data in database with the given user data. Only fields
	 * different from null are updated.
	 * 
	 * @param user
	 * @throws SQLException
	 */
	public void update(User user) throws SQLException {
		if (null == user.getUserId())
			return;
		Map<String, Object> arguments = new HashMap<String, Object>();
		arguments.put("setFragments", new StringBuilder());
		arguments.put("values", new ArrayList<Object>());
		arguments.put("isFirst", true);
		checkUpdateFields(arguments, "login", user.getLogin());
		checkUpdateFields(arguments, "password", user.getPassword());
		checkUpdateFields(arguments, "created_at", user.getCreated_at());
		checkUpdateFields(arguments, "status", user.getStatus());
		StringBuilder sqlRequest = new StringBuilder();
		sqlRequest.append("UPDATE users SET ");
		sqlRequest.append(arguments.get("setFragments"));
		sqlRequest.append(" WHERE user_id = ?");
		String request = sqlRequest.toString();
		int hash = request.hashCode();
		PreparedStatement ps = updatePs.get(hash);
		if (null == ps) {
			System.out.println("Creating PS");
			updatePs.put(hash, ps = connection.prepareStatement(request));
		}
		ps.clearParameters();
		List<Object> params = (List<Object>) arguments.get("values");
		for (int i = 1, length = params.size(); i <= length; i++) {
			if (params.get(i - 1) instanceof String)
				ps.setString(i, (String) params.get(i - 1));
			else if (params.get(i - 1) instanceof Integer)
				ps.setInt(i, (Integer) params.get(i - 1));
			else if (params.get(i - 1) instanceof Date)
				ps.setTimestamp(
						i,
						new java.sql.Timestamp(((Date) params.get(i - 1))
								.getTime()));
		}
		ps.setInt(params.size() + 1, user.getUserId());
		// connection.setAutoCommit(false);
		ps.executeUpdate();
		// connection.commit();
		System.out.println(ps);
	}

	private void checkUpdateFields(Map<String, Object> arguments,
			String fields, Object value) {
		if (null != value) {
			if (!(Boolean) arguments.get("isFirst"))
				((StringBuilder) arguments.get("setFragments")).append(",");
			else
				arguments.put("isFirst", false);
			((StringBuilder) arguments.get("setFragments")).append(fields);
			((StringBuilder) arguments.get("setFragments")).append("=?");

			((List<Object>) arguments.get("values")).add(value);
		}
	}

	public static void main(String[] args) throws SQLException {
		User u = new User();
		u.setUserId(10);
		u.setLogin("wehicles");
		u.setPassword("password");
		u.setStatus(0);
		u.setCreated_at(new Date());
		getInstance().update(u);
		u.setUserId(10);
		u.setLogin("wehicles2");
		u.setPassword("password2");
		u.setStatus(1);
		u.setCreated_at(new Date());
		getInstance().update(u);
		u.setUserId(10);
		u.setLogin(null);
		u.setPassword("password2");
		u.setStatus(null);
		u.setCreated_at(new Date());
		getInstance().update(u);
	}

	/**
	 * Remove the given user from database
	 * 
	 * @param user
	 * @return false if given user has no id defined.
	 */
	public boolean remove(User user) {
		return false;
	}

	PreparedStatement selectById = null;

	/**
	 * Return the associated User for the given id.
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public User select(String userLogin) throws SQLException {
		if (null == selectById)
			selectById = connection
					.prepareStatement("SELECT * FROM users WHERE login=?");
		selectById.setString(1, userLogin);
		ResultSet result = selectById.executeQuery();
		if (result.next()) {
			User u = new User();
			u.setCreated_at(result.getDate("created_at"));
			u.setLogin(result.getString("login"));
			u.setPassword(result.getString("password"));
			u.setStatus(result.getInt("status"));
			/* etc... */
			return u;
		}
		return null;
	}
	
	public User selectByColumn(String column, String value) throws SQLException {
		System.out.println(value);
		if (null == selectById)
			selectById = connection
					.prepareStatement("SELECT * FROM users WHERE `?`LIKE?");
		selectById.setString(1, column);
		selectById.setString(2, value);
		System.out.println(selectById);
		ResultSet result = selectById.executeQuery();
		if (result.next()) {
			User u = new User();
			u.setCreated_at(result.getDate("created_at"));
			u.setLogin(result.getString("login"));
			u.setPassword(result.getString("password"));
			u.setStatus(result.getInt("status"));
			return u;
		}
		return null;
	}

	/**
	 * Return true if the given user already exists in database.
	 * 
	 * @param u
	 * @return
	 * @throws SQLException 
	 */
	public boolean exists(User u) throws SQLException {
		if (null == selectById)
			selectById = connection
					.prepareStatement("SELECT * FROM users WHERE login=?");
		selectById.setString(1, u.getLogin());
		ResultSet result = selectById.executeQuery();
		if (result.next()) {
			return true;
		}
		return false;
	}

	/**
	 * Search into User space for given criterias and make order according to
	 * the given string.
	 * 
	 * @param criteria
	 * @param values
	 *            WHERE name=toto where name is in criteria array and toto in
	 *            object value arrray.
	 * @param order
	 *            should be of "id DESC, name" (SQL Order BY clause form)
	 * @return
	 */
	public List<User> find(String[] criteria, Object[] values, String order) {
		return null;
	}

	PreparedStatement reset = null;

	/**
	 * Clean all record in this table. <b
	 * style="color:red;"><BLINK>Tips</BLINK></b> : be careful !
	 * 
	 * @throws SQLException
	 */
	public void reset() throws SQLException {
		if (null == reset)
			reset = connection.prepareStatement("DELETE FROM users WHERE 1=1");
		reset.executeUpdate();
	}

}